﻿using Base.DAL;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.IO;
using System;
using System.Linq;
using System.Collections.Generic;
using Base.Util;
using System.Text.RegularExpressions;
using System.Data;
using Dapper;
using System.Windows.Forms;

namespace Base.BLL
{
    /// <summary>
    /// 数据库预处理
    /// </summary>
    class DbPretreat
    {
        /// <summary>
        /// 检测连接是否可用
        /// </summary>
        /// <returns></returns>
        public static bool CanConnect()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(Db.StrConnMaster))
                {
                    conn.Open();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 检测数据库是否存在
        /// </summary>
        /// <returns></returns>
        public static bool DbExists()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(Db.StrConnMaster))
                {
                    conn.Open();                    
                    return conn.Query<int>("SELECT count(1) ct FROM  master..sysdatabases WHERE name='"+ Db.StrDbName+"'").FirstOrDefault() > 0;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 获取现有数据库名
        /// </summary>
        public static List<string> GetDbs()
        {
            using (IDbConnection conn = new SqlConnection(Db.StrConnMaster))
            {
                conn.Open();
                return conn.Query<string>("SELECT name FROM  master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' )").ToList();
            }
        }

        /// <summary>
        /// 是否需要预处理
        /// </summary>
        /// <returns></returns>
        public static bool NeedPretreat()
        {
            return Db.Count(@"SELECT COUNT(object_id) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZCSJB_JQ2]') AND type in (N'U')") == 0;
        }


        /// <summary>
        /// 创建空数据库
        /// </summary>
        public static bool CreateDb()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(Db.StrConnMaster))
                {
                    conn.Open();
                    string dir = Application.StartupPath;
                    //conn.Execute(@"IF EXISTS (SELECT name FROM sys.databases WHERE name = N'KJ440KY') DROP DATABASE [KJ440KY];");
                    conn.Execute("restore database KJ440KY from disk='" + dir + "/Tpl/bak.bak'");
                }
                return true;
            }catch(Exception ex)
            {
                return false;
            }
        }


        /// <summary>
        /// 创建新的表、视图、储存过程
        /// </summary>
        public static void AddSchema()
        {
            FileInfo file = new FileInfo("db_update.sql");
            string script = file.OpenText().ReadToEnd().Replace("@tablename___", Db.StrDbName);
            Server srv = new Server(new ServerConnection(new SqlConnection(Db.StrConn)));
            srv.ConnectionContext.ExecuteNonQuery(script);
        }
        /// <summary>
        /// 重新计算加权平均值
        /// </summary>
        /// <param name="tName">要计算的数据表名</param>
        /// <param name="tCol">要计算的数据表时间列名</param>
        /// <param name="spName">调用的存储过程名</param>
        /// <param name="hSpan">加权平均时间</param>
        private static void CalcWeighted(string tName, string tCol, string spName, int hSpan, IPretreatProc proc)
        {
            int[] hSpans = new int[] { 2, 4, 8, 12, 24 };
            if (!hSpans.Contains(hSpan))
            {
                return;
            }
            if (proc != null) proc.ProcNow("统计时间段...\r\n", false, 50);
            DateTime dtStart = Db.Find<DateTime>("SELECT MIN(" + tCol + ") FROM " + tName + "");
            if (dtStart == default(DateTime))
            {
                return;
            }
            DateTime dtEnd = Db.Find<DateTime>("SELECT MAX(" + tCol + ") FROM " + tName + "");
            if (proc != null) proc.ProcNow("时间段统计完毕\r\n计算中 ", false, 50);
            if (hSpan != 2)
            {
                spName += "_By2";
            }
            LoggerHelper.Info("开始计算" + hSpan + "小时加权平均值(" + tName + ")...");
            DateTime dtMid = dtStart;
            try
            {
                int days = (dtEnd - dtStart).Days;
                int day = 0;
                int step = 2;
                do
                {
                    day += step;
                    dtMid = dtStart + new TimeSpan(step * 24, 0, 0);
                    Db.ExecuteSP(spName, new { timeStart = dtStart, timeEnd = dtMid, hSpan = hSpan });
                    dtStart = dtMid;
                    if (proc != null) proc.ProcNow(".", false, (day * 100 / days) > 100 ? 100 : (day * 100 / days));
                    //Console.WriteLine(DateTime.Now);
                } while (dtMid <= dtEnd);
                LoggerHelper.Info(hSpan + "\r\n小时加权平均值(" + tName + ")计算完毕");
            }
            catch (Exception e)
            {
                LoggerHelper.Error(e);
            }

        }
        /// <summary>
        /// 重新计算综采数据加权平均值
        /// </summary>
        /// <param name="hSpan">加权平均时间</param>
        public static void CalcZcWeighted(int hSpan, IPretreatProc proc = null)
        {
            CalcWeighted("ZCSJB", "ZDATETIME", "Procedure_ZC_Pretreat_JQ", hSpan, proc);
        }
        /// <summary>
        /// 重新计算顶板离层数据加权平均值
        /// </summary>
        /// <param name="hSpan">加权平均时间</param>
        public static void CalcDblcWeighted(int hSpan, IPretreatProc proc = null)
        {
            CalcWeighted("DBLCSJB", "LCDATETIME", "Procedure_DBLC_Pretreat_JQ", hSpan, proc);
        }
        /// <summary>
        /// 重新计算锚杆数据加权平均值
        /// </summary>
        /// <param name="hSpan">加权平均时间</param>
        public static void CalcMgWeighted(int hSpan, IPretreatProc proc = null)
        {
            CalcWeighted("MGYLSJB", "MDATETIME", "Procedure_MG_Pretreat_JQ", hSpan, proc);
        }
        /// <summary>
        /// 重新计算超前应力数据加权平均值
        /// </summary>
        /// <param name="hSpan">加权平均时间</param>
        public static void CalcCqylWeighted(int hSpan, IPretreatProc proc = null)
        {
            CalcWeighted("CQYLSJB", "CQDATETIME", "Procedure_CQYL_Pretreat_JQ", hSpan, proc);
        }
        /// <summary>
        /// 数据预处理
        /// </summary>
        public static void Pretreat(IPretreatProc proc = null)
        {
            try
            {
                string tbName = Db.StrDbName;
                if (proc != null) proc.ProcNow("正在添加必要的结构...\r\n", false, 50);
                AddSchema();
                if (proc != null) proc.ProcNow("结构添加完毕\r\n", false, 100);
                int[] hSpans = new int[] { 2, 4, 8, 12, 24 };
                string[] tCnNames = new string[] { "综采", "顶板离层/下沉", "锚杆/索", "超前应力" };
                string[] tNames = new string[] { "ZCSJB", "DBLCSJB", "MGYLSJB", "CQYLSJB" };
                string[] tCols = new string[] { "ZDATETIME", "LCDATETIME", "MDATETIME", "CQDATETIME" };
                string[] spNames = new string[] { "Procedure_ZC_Pretreat_JQ", "Procedure_DBLC_Pretreat_JQ", "Procedure_MG_Pretreat_JQ", "Procedure_CQYL_Pretreat_JQ" };
                //切换日志到简单记录模式，避免出现日志存储满的情况
                Db.Execute("ALTER DATABASE " + tbName + " SET RECOVERY SIMPLE;DBCC SHRINKDATABASE(" + tbName + ", 0);ALTER DATABASE " + tbName + " SET RECOVERY BULK_LOGGED;");
                for (int i = 0; i < tCnNames.Length; i++)
                {
                    int count = 0;
                    foreach (var hSpan in hSpans)
                    {
                        if (proc != null) proc.ProcNow("正在预估" + tCnNames[i] + hSpan + "小时加权平均值计算时间...\r\n", false, 50);
                        if (hSpan == 2)
                        {
                            count = Db.Query<int>("SELECT COUNT(1) FROM " + tNames[i]).FirstOrDefault();
                        }
                        else if (count == 0)
                        {
                            count = Db.Query<int>("SELECT COUNT(1) FROM " + tNames[i] + "_JQ2").FirstOrDefault();
                        }
                        if (proc != null) proc.ProcNow("共" + count + "条数据，预计需要" + (int)(count / 9304985.0 * 8 + 1) + "分钟时间\r\n", false, 100);
                        if (hSpan == 2) count = 0;
                        if (proc != null) proc.ProcNow("正在计算" + tCnNames[i] + hSpan + "小时加权平均数据...\r\n", false, 0);
                        CalcWeighted(tNames[i], tCols[i], spNames[i], hSpan, proc);
                        if (proc != null) proc.ProcNow(tCnNames[i] + hSpan + "小时加权平均数据计算完毕\r\n", false, 100);
                    }
                }
                //切换日志到完全模式
                Db.Execute("ALTER DATABASE " + tbName + " SET RECOVERY FULL;");
                if (proc != null) proc.ProcNow("处理完毕\r\n", true, 100);
            }
            catch (Exception ex)
            {
                if (proc != null) proc.ProcNow("发生错误，异常退出\r\n", true, 0);
                LoggerHelper.Error(ex);
            }
        }

        public static void Test()
        {
            DbPretreat.AddSchema();
        }
    }
    /// <summary>
    /// 接收预处理进度的接口
    /// </summary>
    interface IPretreatProc
    {
        /// <summary>
        /// 指示预处理进展到哪个阶段
        /// </summary>
        /// <param name="msg">阶段描述</param>
        /// <param name="isEnd">是否已结束</param>
        /// <param name="percent">进度百分比</param>
        void ProcNow(string msg, bool isEnd, int percent);
    }
}
